#!/usr/bin/env python3
# -*- coding: utf-8 -*-
"""
选股结果表模型 (SelectResult)
"""

from sqlalchemy import Column, Integer, String, Float, DateTime, UniqueConstraint, Index
from sqlalchemy.sql import func
from datetime import datetime
from typing import List, Optional

from .BaseModel import BaseModel, standardize_date_format

class SelectResult(BaseModel):
    """选股结果表模型"""
    __tablename__ = 'select_result'
    
    id = Column(Integer, primary_key=True, autoincrement=True)
    execution_date = Column(String(10), nullable=False, index=True)
    strategy_type = Column(String(50), nullable=False, index=True)
    security_id = Column(String(20), nullable=False, index=True)
    security_name = Column(String(100))
    latest_price = Column(Float)
    change_percent = Column(Float)
    industry = Column(String(50))
    limit_up_20d = Column(Integer, default=0)
    limit_up_60d = Column(Integer, default=0)
    last_limit_up_date = Column(String(10))
    net_inflow_3d = Column(Float)
    big_net_inflow_3d = Column(Float)
    created_at = Column(DateTime, default=func.current_timestamp())
    
    # 唯一约束
    __table_args__ = (
        UniqueConstraint('execution_date', 'strategy_type', 'security_id', name='uq_execution_strategy_security'),
        Index('idx_execution_strategy', 'execution_date', 'strategy_type'),
    )
    
    @classmethod
    def get_session(cls):
        """获取数据库会话"""
        return cls._db_manager.get_stock_selection_session()
    
    @classmethod
    def _get_existing_record(cls, session, data):
        """获取现有记录"""
        return session.query(cls).filter(
            cls.execution_date == data['execution_date'],
            cls.strategy_type == data['strategy_type'],
            cls.security_id == data['security_id']
        ).first()
    
    @classmethod
    def _update_existing_record(cls, existing, data):
        """更新现有记录"""
        existing.security_name = data.get('security_name', existing.security_name)
        existing.latest_price = data.get('latest_price', existing.latest_price)
        existing.change_percent = data.get('change_percent', existing.change_percent)
        existing.industry = data.get('industry', existing.industry)
        existing.limit_up_20d = data.get('limit_up_20d', existing.limit_up_20d)
        existing.limit_up_60d = data.get('limit_up_60d', existing.limit_up_60d)
        existing.last_limit_up_date = data.get('last_limit_up_date', existing.last_limit_up_date)
        existing.net_inflow_3d = data.get('net_inflow_3d', existing.net_inflow_3d)
        existing.big_net_inflow_3d = data.get('big_net_inflow_3d', existing.big_net_inflow_3d)
    
    @classmethod
    def _to_dict(cls, result):
        """将结果转换为字典"""
        return {
            'id': result.id,
            'execution_date': result.execution_date,
            'strategy_type': result.strategy_type,
            'security_id': result.security_id,
            'security_name': result.security_name,
            'latest_price': result.latest_price,
            'change_percent': result.change_percent,
            'industry': result.industry,
            'limit_up_20d': result.limit_up_20d,
            'limit_up_60d': result.limit_up_60d,
            'last_limit_up_date': result.last_limit_up_date,
            'net_inflow_3d': result.net_inflow_3d,
            'big_net_inflow_3d': result.big_net_inflow_3d,
            'created_at': result.created_at
        }
    
    @classmethod
    def get_results(cls, execution_date: Optional[str] = None,
                   strategy_types: Optional[List[str]] = None,
                   limit: int = 1000) -> List['SelectResult']:
        """获取选股结果"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            
            query = session.query(cls)
            
            if execution_date:
                query = query.filter(cls.execution_date == execution_date)
            
            if strategy_types:
                query = query.filter(cls.strategy_type.in_(strategy_types))
            
            query = query.order_by(cls.execution_date.desc(), cls.strategy_type, cls.security_id)
            query = query.limit(limit)
            
            results = query.all()
            session.close()
            return results
            
        except Exception as e:
            # 获取选股结果失败: {str(e)}
            return []
    
    @classmethod
    def save_batch(cls, results_data: List[dict]) -> bool:
        """批量保存选股结果"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        session = cls.get_session()
        success_count = 0
        
        try:
            for data in results_data:
                existing_record = cls._get_existing_record(session, data)
                
                if existing_record:
                    cls._update_existing_record(existing_record, data)
                else:
                    new_record = cls(**data)
                    session.add(new_record)
                
                success_count += 1
            
            session.commit()
            # 成功保存 {success_count} 条选股结果
            return True
            
        except Exception as e:
            session.rollback()
            # 保存选股结果失败: {str(e)}
            return False
        finally:
            session.close()
    
    @classmethod
    def get_available_dates(cls) -> List[str]:
        """获取可用的执行日期列表"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            dates = session.query(cls.execution_date).distinct().order_by(cls.execution_date.desc()).all()
            session.close()
            return [date[0] for date in dates]
        except Exception as e:
            # 获取可用日期失败: {str(e)}
            return []
    
    @classmethod
    def get_available_strategies(cls) -> List[str]:
        """获取可用的策略类型列表"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            strategies = session.query(cls.strategy_type).distinct().order_by(cls.strategy_type).all()
            session.close()
            return [strategy[0] for strategy in strategies]
        except Exception as e:
            # 获取可用策略失败: {str(e)}
            return []
    
    @classmethod
    def delete_results(cls, execution_date: str, strategy_types: Optional[List[str]] = None) -> int:
        """删除指定条件的选股结果"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        session = cls.get_session()
        
        try:
            query = session.query(cls).filter(cls.execution_date == execution_date)
            
            if strategy_types:
                query = query.filter(cls.strategy_type.in_(strategy_types))
            
            deleted_count = query.delete()
            session.commit()
            # 成功删除 {deleted_count} 条选股结果
            return deleted_count
            
        except Exception as e:
            session.rollback()
            # 删除选股结果失败: {str(e)}
            return 0
        finally:
            session.close()
    
    @classmethod
    def get_result_summary(cls, execution_date: str) -> dict:
        """获取指定日期的选股结果汇总"""
        if not cls._db_manager:
            raise ValueError("数据库管理器未设置，请先调用 set_db_manager()")
        
        try:
            session = cls.get_session()
            
            # 获取总记录数
            total_count = session.query(cls).filter(cls.execution_date == execution_date).count()
            
            # 获取策略分布
            strategy_counts = session.query(cls.strategy_type, func.count(cls.id)).filter(
                cls.execution_date == execution_date
            ).group_by(cls.strategy_type).all()
            
            # 获取行业分布
            industry_counts = session.query(cls.industry, func.count(cls.id)).filter(
                cls.execution_date == execution_date,
                cls.industry.isnot(None)
            ).group_by(cls.industry).all()
            
            session.close()
            
            return {
                'execution_date': execution_date,
                'total_count': total_count,
                'strategy_distribution': {strategy: count for strategy, count in strategy_counts},
                'industry_distribution': {industry: count for industry, count in industry_counts}
            }
            
        except Exception as e:
            # 获取选股结果汇总失败: {str(e)}
            return {}
